package com.sl.au.service.impl;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.stream.Collectors;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.sl.au.entity.Chance;
import com.sl.au.entity.ChanceStatistics;
import com.sl.au.entity.DateQueryItem;
import com.sl.au.entity.MFOldPartRelation;
import com.sl.au.entity.Root;
import com.sl.au.entity.Section;
import com.sl.au.entity.User;
import com.sl.au.repository.ChanceDeliveryRepository;
import com.sl.au.repository.ChanceDeliveryTempRepository;
import com.sl.au.repository.ChanceRepository;
import com.sl.au.service.ChanceService;
import com.sl.au.service.QueryService;
import com.sl.au.service.SectionService;

@Service("SaleChanceService")
@Transactional
public class ChanceServiceImpl extends BaseServiceImpl implements ChanceService {

	@Autowired
	ChanceRepository saleChanceRepository;

	@Autowired
	ChanceDeliveryRepository chanceDeliveryRepository;

	@Autowired
	ChanceDeliveryTempRepository chanceDeliveryTempRepository;

	@Autowired
	QueryService queryService;

	@Autowired
	SectionService sectionService;

	public ChanceRepository getRepository() {
		return this.saleChanceRepository;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Chance> getSaleChances(int start, int length, Map<String, String> condition, String orderName,
			String dir) {
		// Page<Chance> pages = null;
		// Pageable pageable = new PageRequest(start / length, length,
		// Sort.Direction.DESC, "createTime");
		// pages =
		// saleChanceRepository.findSearchChance(condition.get("userId"),
		// condition.get("key"), pageable);
		// return pages.getContent();
		String key = condition.get("key");
		String state = condition.get("state");
		key = key.replaceAll("\\s+", "");
		StringBuilder hql = new StringBuilder("select c from Chance c where (c.user.id ='" + condition.get("userId")
				+ "' " + "and (c.customerId like '%" + key + "%' or c.name like '%" + key + "%' " + "or c.source like '%" + key
				+ "%' or c.sourceUnit like '%" + key + "%' " + "or c.orderUnit like '%" + key
				+ "%' or c.orderPerson like '%" + key + "%' " + "or c.projectState like '%" + key + "%'"
				+ "or c.user.empLogin.userName like '%" + key + "%')");
		if(state != null && state.equals("已提交")){
			hql.append(" and c.isSubmit = 1 and isSucceed is null");
		}else if(state != null && state.equals("未提交")){
			hql.append(" and c.isSubmit = 0 and isSucceed is null");
		}else if(state != null && state.equals("已关闭")){
			hql.append(" and isSucceed is not null");
		}
		if (key.indexOf("已提交") != -1) {
			hql.append(" or c.isSubmit = 1");
		} else if (key.indexOf("未提交") != -1) {
			hql.append(" or c.isSubmit = 0");
		}
		hql.append(" or c.isSucceed = '" + key + "') order by " + orderName + " " + dir);
		Query query = queryService.createQueryHql(hql.toString());
		query.setFirstResult(start).setMaxResults(length);
		return query.getResultList();
	}

	@Override
	public int getSaleChanceCount(Map<String, String> condition) {
		String key = condition.get("key");
		String state = condition.get("state");
		key = key.replaceAll("\\s+", "");
		StringBuilder hql = new StringBuilder("select c from Chance c where c.user.id ='" + condition.get("userId")
				+ "' " + "and (c.customerId like '%" + key + "%' or c.name like '%" + key + "%' " + "or c.source like '%" + key
				+ "%' or c.sourceUnit like '%" + key + "%' " + "or c.orderUnit like '%" + key
				+ "%' or c.orderPerson like '%" + key + "%' " + "or c.projectState like '%" + key + "%'"
				+ "or c.user.empLogin.userName like '%" + key + "%')");
		if(state != null && state.equals("已提交")){
			hql.append(" and c.isSubmit = 1 and isSucceed is null");
		}else if(state != null && state.equals("未提交")){
			hql.append(" and c.isSubmit = 0 and isSucceed is null");
		}else if(state != null && state.equals("已关闭")){
			hql.append(" and isSucceed is not null");
		}
		
		if (key.indexOf("已提交") != -1) {
			hql.append(" or c.isSubmit = 1");
		} else if (key.indexOf("未提交") != -1) {
			hql.append(" or c.isSubmit = 0");
		}
		hql.append(" or c.isSucceed = '" + key + "')");
		Query query = queryService.createQueryHql(hql.toString());
		return query.getResultList().size();
	}

	@Override
	public List<Chance> getQueryChances(int start, int length, Map<String, String> condition, User user,
			String orderName, String dir) {
		Page<Chance> pages = null;
		Pageable pageable = new PageRequest(start / length, length, Sort.Direction.DESC, "createTime");
		String dataFilter = condition.get("dataFilter");
		if (dataFilter.equals("本部门")) {
			pages = saleChanceRepository.findQueryChanceDelivery(user.getSaleArea().getSuperior(), condition.get("key"),
					pageable);
		} else if (dataFilter.equals("本片区")) {
			pages = saleChanceRepository.findQueryChanceManager(user.getSaleArea().getId(), condition.get("key"),
					pageable);
		} else if (dataFilter.equals("所有")) {
			pages = saleChanceRepository.findQueryChanceAll(condition.get("key"), pageable);
		} else {
			pages = saleChanceRepository.findQueryChanceUser(user.getId(), condition.get("key"), pageable);
		}
		return pages.getContent();
	}

	@Override
	public Long getQueryCount(Map<String, String> condition, User user) {
		List<Chance> listCount;
		Long count = (long) 0;
		String dataFilter = condition.get("dataFilter");
		if (dataFilter.equals("本部门")) {
			listCount = saleChanceRepository.getQueryDeliveryCount(user.getSaleArea().getSuperior(),
					condition.get("key"));
			count = (long) listCount.size();
		} else if (dataFilter.equals("本片区")) {
			listCount = saleChanceRepository.getQueryManagerCount(user.getSaleArea().getId(), condition.get("key"));
			count = (long) listCount.size();
		} else if (dataFilter.equals("所有")) {
			count = saleChanceRepository.getQueryAllCount(condition.get("key"));
		} else {
			listCount = saleChanceRepository.getQueryUserCount(user.getId(), condition.get("key"));
			count = (long) listCount.size();
		}
		return count;
	}

	@Override
	public List<Chance> getCustomerChances(int start, int length, String customerId, String key) {
		if (key == null) {
			key = "";
		} else {
			key = key.replaceAll("\\s+", "");
		}
		Page<Chance> pages = null;
		Pageable pageable = new PageRequest(start / length, length, Sort.Direction.DESC, "createTime");
		pages = saleChanceRepository.findCustomerChance(customerId, key, pageable);
		return pages.getContent();
	}

	@Override
	public long getCustomerChancesCount(String customerId, String key) {
		if (key == null) {
			key = "";
		} else {
			key = key.replaceAll("\\s+", "");
		}
		long count = saleChanceRepository.getCustomerChanceCount(customerId, key);
		return count;
	}

	@Override
	public List<Chance> getQueryChancesHql(int start, int length, Map<String, Object> condition, User user,
			String orderName, String dir) {
		Map<String, Object> namedParamsMap = new HashMap<String, Object>();	
		
		Map<String, Object> namedParamsMap2 = new HashMap<String, Object>();
		@SuppressWarnings("unchecked")
		Map<String,Object> qMap=(Map<String, Object>) condition.get("qMap");
		Map<String,Object> qMap2=new HashMap<String, Object>();
		Map<String,Object> condition2=new HashMap<String, Object>();
		int stars=0;
		for (String key : qMap.keySet()) {			
			Object o=qMap.get(key);
			if(key.contains("chancePriority")) {
				stars=o.toString().length();				
				break;
			}		
		}
		if(stars>0){			
					qMap.remove("chancePriority");				
		}
		condition2.put("qMap", qMap2);
		
		
		
		StringBuilder hql = queryService.buildCombinedQueryHql("Chance", null, condition, namedParamsMap,null, user);
		
		for (String key : qMap.keySet()) {			
			Object o=qMap.get(key);		
			if(! (o instanceof DateQueryItem)){
				qMap2.put("saleChance." + key, namedParamsMap.get(key));
			    
			}else{
				qMap2.put(UUID.randomUUID().toString(),(DateQueryItem)o);
			}
		}
		
		
		String dataFilter = condition.get("dataFilter").toString();
		if (StringUtils.hasText(dataFilter))
			this.chanceDataFilter(hql, dataFilter, user);
		else
			return null;

		/*Map<String, Object> map2 = new HashMap<String, Object>();
		Map<String, Object> qMap2 = new HashMap<String, Object>();*/

		StringBuilder hql2 = null;
		
		if (dataFilter.equals("本部门")) {
			hql2 = queryService.buildCombinedQueryHql("ChanceDeliveryTemp", "saleChance", condition2, namedParamsMap2,null, user);
		} else if (dataFilter.equals("本片区")|| dataFilter.equals("本科室")) {
			hql2 = queryService.buildCombinedQueryHql("ChanceDelivery", "saleChance", condition2, namedParamsMap2,null, user);
		} else if (dataFilter.equals("本人")) {
			hql2 = queryService.buildCombinedQueryHql("ChanceManagerDelivery", "saleChance", condition2, namedParamsMap2,null,
					user);
		}
		if (StringUtils.hasText(dataFilter))
			this.chanceTempDataFilter(hql2, dataFilter, user);
		else
			return null;

		if(stars!=0){
			hql.append(" and (length(e.chancePriority)="+stars+")");
		}
		
		
		
		List<Chance> list = new ArrayList<>();
		Query query = queryService.createQueryHql(hql.toString());
		list = queryService.executeHql(query, namedParamsMap);
		if (null != hql2) {
			if(stars!=0){
				hql2.append(" and (length(e.saleChance.chancePriority)="+stars+")");
			}
			query = queryService.createQueryHql(hql2.toString());
			List<Chance> list2 = queryService.executeHql(query, namedParamsMap2);
			list.addAll(list2);
		}

		return list.stream().distinct().sorted(Comparator.comparing(Chance::getCreateTime)).skip(start).limit(length)
				.collect(Collectors.toList());
	}

	private void chanceDataFilter(StringBuilder hql, String dataFilter, User user) {
		// 数据范围过滤
		String sectionId = "";
		Section s = sectionService.getRepository().findByChief(user);
		if (s != null) {
			sectionId = s.getId();
		}
		if (dataFilter.equals("本部门")) {
			hql.append(" and (e.user.saleArea.superior='" + user.getSaleArea().getSuperior() + "') ")
					.append(" and (e.isSubmit=1) ");
		} else if (dataFilter.equals("本片区")) {
			hql.append(" and (e.user.saleArea.id='" + user.getSaleArea().getId() + "') ");
			hql.append(" and (e.isSubmit=1) ");
		} else if (dataFilter.equals("本科室")) {
			hql.append(" and (e.user.saleArea.id in (select s.saleArea.id from SectionArea s where s.section.id= '"
					+ sectionId + "')) ");
			hql.append(" and (e.isSubmit=1) ");
		} else if (dataFilter.equals("所有")) {
			hql.append(" and (e.isSubmit=1) ");
		} else {
			hql.append(" and (e.user.id='" + user.getId() + "') ");
			hql.append(" and (e.isSubmit=1) ");
		}
	}

	private void chanceTempDataFilter(StringBuilder hql, String dataFilter, User user) {
		// 数据范围过滤
		@SuppressWarnings("unused")
		String sectionId = "";
		Section s = sectionService.getRepository().findByChief(user);
		if (s != null) {
			sectionId = s.getId();
		}
		if (dataFilter.equals("本部门")) {
			hql.append(" and (e.receiveSuperior='" + user.getSaleArea().getSuperior() + "') ");

		}else if (dataFilter.equals("本片区")||dataFilter.equals("本科室")) {
			hql.append(" and (e.saleArea.id='" + user.getSaleArea().getId() + "') ");
			hql.append(" and (e.isReceived=1) ");
		} else if (dataFilter.equals("本人")) {
			hql.append(" and (e.user.id='" + user.getId() + "') ");
		}
	}

	@Override
	public long getQueryChancesCountHql(Map<String, Object> condition, User user) {

	Map<String, Object> namedParamsMap = new HashMap<String, Object>();	
		
		Map<String, Object> namedParamsMap2 = new HashMap<String, Object>();
		@SuppressWarnings("unchecked")
		Map<String,Object> qMap=(Map<String, Object>) condition.get("qMap");
		Map<String,Object> qMap2=new HashMap<String, Object>();
		Map<String,Object> condition2=new HashMap<String, Object>();
		int stars=0;
		for (String key : qMap.keySet()) {			
			Object o=qMap.get(key);
			if(key.contains("chancePriority")) {
				stars=o.toString().length();				
				break;
			}		
		}
		if(stars>0){			
					qMap.remove("chancePriority");				
		}
		condition2.put("qMap", qMap2);
		
		
		
		StringBuilder hql = queryService.buildCombinedQueryHql("Chance", null, condition, namedParamsMap,null, user);
		
		for (String key : qMap.keySet()) {			
			Object o=qMap.get(key);		
			if(! (o instanceof DateQueryItem)){
				qMap2.put("saleChance." + key, namedParamsMap.get(key));
			    
			}else{
				qMap2.put(UUID.randomUUID().toString(),(DateQueryItem)o);
			}
		}
		
		
		String dataFilter = condition.get("dataFilter").toString();
		if (StringUtils.hasText(dataFilter))
			this.chanceDataFilter(hql, dataFilter, user);
		else
			return 0;

		/*Map<String, Object> map2 = new HashMap<String, Object>();
		Map<String, Object> qMap2 = new HashMap<String, Object>();*/

		StringBuilder hql2 = null;
		
		if (dataFilter.equals("本部门")) {
			hql2 = queryService.buildCombinedQueryHql("ChanceDeliveryTemp", "saleChance", condition2, namedParamsMap2,null, user);
		} else if (dataFilter.equals("本片区")|| dataFilter.equals("本科室")) {
			hql2 = queryService.buildCombinedQueryHql("ChanceDelivery", "saleChance", condition2, namedParamsMap2,null, user);
		} else if (dataFilter.equals("本人")) {
			hql2 = queryService.buildCombinedQueryHql("ChanceManagerDelivery", "saleChance", condition2, namedParamsMap2,null,
					user);
		}
		if (StringUtils.hasText(dataFilter))
			this.chanceTempDataFilter(hql2, dataFilter, user);
		else
			return 0;

		if(stars!=0){
			hql.append(" and (length(e.chancePriority1)="+stars+")");
		}
		
		
		
		List<Chance> list = new ArrayList<>();
		Query query = queryService.createQueryHql(hql.toString());
		list = queryService.executeHql(query, namedParamsMap);
		if (null != hql2) {
			if(stars!=0){
				hql2.append("  and (length(e.saleChance.chancePriority)="+stars+")");
			}
			query = queryService.createQueryHql(hql2.toString());
			List<Chance> list2 = queryService.executeHql(query, namedParamsMap2);
			list.addAll(list2);
		}

		return list.stream().distinct().collect(Collectors.toList()).size();
	}
	@PersistenceContext
	private EntityManager em;
	
	@Override
	public List<ChanceStatistics> getChanceStatistics(Integer year, int i, String superior) {
		Query query = this.em.createNativeQuery("{call getChanceCountInfo(?,?,?)}",ChanceStatistics.class);
		query.setParameter(1, year);
		query.setParameter(2, i);
		query.setParameter(3, superior);
		
		@SuppressWarnings("unchecked")
		List<ChanceStatistics> users = query.getResultList();
		return users;
	}

}
